const express=require('express')
const router=express.Router()
const pool=require('../pool')
const {host} = require('../router/host')
const query=require('../router/utils')
const multer=require('multer')
const {   
    resSuccessData,
    resSuccess,
    resError} = require('../utils/index')

router.post('/list',(req,res)=>{
    pool.query('select * from category',(err,result)=>{
        if(err) throw err
        res.send({code:1,data:result});
    })
})
// 新增分类
router.post('/add',async (req,res)=>{
    let {category,english}=req.body
    let sql='insert into category (category,english) values (?,?)';
    let ret=await query(sql,[category,english])
    if(ret.affectedRows>0){
        res.send({code:1,msg:'增加成功'})
    }
})
//删除分类接口
router.post('/del',async(req,res)=>{
    // 传入数组
    let dels = req.body;
    // console.log(dels)
    if(dels.length){
        dels = dels.join(',');
        // 删除
        let sql = `delete from category where id in(${dels})`;
        const ret = await query(sql,[]);
        if(ret.affectedRows>0){
            resSuccess(res)
        }else{
            resError(res,'删除失败')
        }
    }else{
        resError(res,'删除参数不能为空')
    }
})
// 获取分类详情
router.post('/category',async (req,res)=>{
    let id=req.body.id
    let sql="select * from category where id=?"
    let ret =await query(sql,[id])
    if(ret.length){
        res.send({code:1,data:ret[0]})
    }else{
        res.send({code:0,msg:'查找失败'})
    }
})
// 编辑后的分类上传
router.post('/update',async (req,res)=>{
    let {category,english,id}=req.body
    let sql='update category set category=?,english=? where id=?'
    let ret=await query(sql,[category,english,id])
    if(ret.affectedRows>0){
        resSuccess(res)
    }else{
        resError(res,'修改失败')
    }
    
})

// 获得分类下曲谱列表
router.post('/detailList',(req,res)=>{
    let page=req.body.page
    let keywords=req.body.keywords
    let condition=''
    let params=[]
    if(keywords){
        condition=`where d.song like '%${keywords}%'`
    }
    let count=6
    let start=(parseInt(page)-1)*count
    let sql=`select d.id,d.song,d.photo,c.category from detail as d inner join category as c on d.c_id=c.id ${condition} limit ${start},${count} `
    pool.query(sql,params,(err,result)=>{
        if(err) throw err
        result.forEach(el => {
            el.photo=host+"/"+el.photo
        });
        if(keywords){
            condition=`where song like '%${keywords}%'`
        }
        sql=`select count(id) as count from detail ${condition}`
        pool.query(sql,[],(err,result1)=>{
            if(err) throw err
            res.send({code:1,data:result,total:result1[0].count})
        })
    })
})
// 上传分类下的乐谱图片
var storage2 = multer.diskStorage({
    destination: function (req, file, cb) {
        cb(null, 'image/detail/')
    },
    filename: function (req, file, cb) {
        cb(null, file.originalname)
    }
})
var upload3 = multer({ storage: storage2 })
router.post('/addDetailFile', upload3.single('file'),async (req, res) => {
    // body 包含表单信息
    const photo = req.file.path.replace(/\\/g, '/').slice(6);
    // 将这个数据返回给前端,前端上传查到数据
    res.send(photo)

})
// 增加曲谱
router.post('/detailAdd',async (req,res)=>{
    let {song,photo,c_id}=req.body
    let sql='insert into detail (song,photo,c_id) values (?,?,?)';
    let ret=await query(sql,[song,photo,c_id])
    if(ret.affectedRows>0){
        res.send({code:1,msg:'增加成功'})
    }
})
// 删除曲谱
router.post('/detailDel',async(req,res)=>{
     // 传入数组
     let dels = req.body;
     // console.log(dels)
     if(dels.length){
         dels = dels.join(',');
         // 删除
         let sql = `delete from detail where id in(${dels})`;
         const ret = await query(sql,[]);
         if(ret.affectedRows>0){
             resSuccess(res)
         }else{
             resError(res,'删除失败')
         }
     }else{
         resError(res,'删除参数不能为空')
     }
})
// 获取特定曲谱的详情
router.post('/detailDetail',async(req,res)=>{
    let {id}=req.body
    let sql='select * from detail where id=?'
    let ret=await query(sql,[id])
    if(ret.length){
        res.send({code:1,data:ret[0]})
    }else{
        res.send({code:0,msg:'查找失败'})
    }
})
// 修改后的曲谱上传
router.post('/detailUpdate',async(req,res)=>{
    let {song,photo,c_id,id}=req.body
    let sql='update detail set song=?,photo=?,c_id=? where id=?'
    let ret=await query(sql,[song,photo,c_id,id])
    if(ret.affectedRows>0){
        resSuccess(res)
    }else{
        resError(res,'修改失败')
    }
    
})



module.exports=router